﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Security.Cryptography;
using RESTAURANT.Class;

namespace RESTAURANT
{
    public class UsingSql
    {
        public static Configurations myConfig = new Configurations(Application.StartupPath + "\\khoisangviet.dat");
        public static SqlConnection myConn = new SqlConnection(myConfig.ConnectionString);

        public static string EmployeeID = Guid.NewGuid().ToString();
        public static string EmployeeName = "";
        public static string Admin = "Ad";
        public static int spacetime = 120, maxtime = 480, latetime = 10, earlytime = 10, monthbegin = 10;
        public static bool logtime = true;

        public static bool Refresh()
        {
            myConfig = new Configurations(Application.StartupPath + "\\khoisangviet.dat");
            myConn = new SqlConnection(myConfig.ConnectionString);
            try
            {
                myConn.Open();
                myConn.Close();
                return true;
            }
            catch (Exception)
            {
                myConn = null;
                return false;
            }
        }

        public static DataTable GetTable(string table)
        {
            try
            {
                if (myConn.State == ConnectionState.Open)
                    myConn.Close();
                myConn.Open();
                DataTable dataTable = new DataTable(table);
                string varselect = "Select * From ";
                varselect += table;
                SqlCommand sqlCommand = new SqlCommand(varselect, myConn);
                SqlDataReader sqlReader = sqlCommand.ExecuteReader();
                dataTable.Load(sqlReader);
                myConn.Close();
                return dataTable;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public static DataTable GetQuery(string sql)
        {
            try
            {
                if (myConn.State == ConnectionState.Open)
                    myConn.Close();
                myConn.Open();
                DataTable dataTable = new DataTable();
                SqlCommand sqlCommand = new SqlCommand(sql);
                sqlCommand.Connection = myConn;
                sqlCommand.CommandType = CommandType.Text;
                SqlDataReader sqlReader = sqlCommand.ExecuteReader();
                dataTable.Load(sqlReader);
                myConn.Close();
                return dataTable;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public static int Proccess(string sql)
        {
            try
            {
                if (myConn.State == ConnectionState.Open)
                    myConn.Close();
                myConn.Open();
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.Connection = myConn;
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.CommandText = sql;
                int kq = sqlCommand.ExecuteNonQuery();
                myConn.Close();
                return kq;
            }
            catch (Exception)
            {
                return -1;
            }
        }

        public static void LoadTableSet()
        {
            try
            {
                DataTable dt = GetTable("[set]");
                spacetime = Convert.ToInt32(dt.Rows[0]["spacetime"]);
                maxtime = Convert.ToInt32(dt.Rows[0]["maxtime"]);
                latetime = Convert.ToInt32(dt.Rows[0]["latetime"]);
                earlytime = Convert.ToInt32(dt.Rows[0]["earlytime"]);
                monthbegin = Convert.ToInt32(dt.Rows[0]["monthbegin"]);
                logtime = Convert.ToBoolean(dt.Rows[0]["logtime"]);
            }
            catch (Exception)
            {
            }
        }

        public static String Encrypt(string str)
        {
            if (str == "") return "";
            byte[] end, encode = UnicodeEncoding.ASCII.GetBytes(str);
            SHA1 sha = new SHA1CryptoServiceProvider();
            end = sha.ComputeHash(encode);
            return BitConverter.ToUInt64(end, 0).ToString();
        }

        #region DEPARTMENT
        public static int GetDepartmentIDMax()
        {
            int kq = 1;
            string sql = "select isnull(max(departmentid),0) from department";
            DataTable dt = GetQuery(sql);
            kq = Convert.ToInt32(dt.Rows[0][0]) + 1;
            return kq;
        }

        public static DataTable getDepartmentList()
        {
            string sql = string.Format(@"select DepartmentCode, DepartmentName from Department where Deleted='false'");
            return GetQuery(sql);
        }

        public static int insert_Department(Department d)
        {
            string sql = string.Format(@"insert into department(departmentid,departmentcode,departmentname,note,deleted) values(N'{0}',N'{1}',N'{2}',N'{3}',N'{4}')"
                , d.DepartmentID, d.DepartmentCode, d.DepartmentName, d.Note, d.Deleted);
            return Proccess(sql);
        }

        public static int update_Department(Department d)
        {
            string sql = string.Format(@"update department set departmentcode=N'{0}',departmentname=N'{1}',note=N'{2}' where departmentid='{3}'"
                , d.DepartmentCode, d.DepartmentName, d.Note, d.DepartmentID);
            return Proccess(sql);
        }

        public static int delete_Department(string departmentID)
        {
            string sql = string.Format(@"update department set deleted='true' where departmentID='{0}'", departmentID);
            return Proccess(sql);
        }
        #endregion

        #region POSITION
        public static int GetPositionIDMax()
        {
            int kq = 1;
            string sql = "select isnull(max(positionid),0) from position";
            DataTable dt = GetQuery(sql);
            kq = Convert.ToInt32(dt.Rows[0][0]) + 1;
            return kq;
        }

        public static DataTable getPositionList()
        {
            string sql = string.Format(@"select PositionCode, PositionName from Position where Deleted='false'");
            return GetQuery(sql);
        }

        public static int insert_position(Position p)
        {
            string sql = string.Format(@"insert into position(positionid,positioncode,positionname,note,deleted)values(N'{0}',N'{1}',N'{2}',N'{3}',N'{4}')"
                , p.PositionID, p.PositionCode, p.PositionName, p.Note, p.Deleted);
            return Proccess(sql);
        }

        public static int update_position(Position p)
        {
            string sql = string.Format(@"update position set positioncode=N'{0}',positionname=N'{1}',note=N'{2}' where positionid='{3}'"
                , p.PositionCode, p.PositionName, p.Note, p.PositionID);
            return Proccess(sql);
        }

        public static int delete_position(string posotionID)
        {
            string sql = string.Format(@"update position set deleted='true' where positionid='{0}'", posotionID);
            return Proccess(sql);
        }
        #endregion

        #region SHIFT
        public static int GetShiftIDMax()
        {
            int kq = 1;
            string sql = "select isnull(max(shiftid),0) from shift";
            DataTable dt = GetQuery(sql);
            kq = Convert.ToInt32(dt.Rows[0][0]) + 1;
            return kq;
        }

        public static DataTable getShiftList()
        {
            string sql = string.Format(@"select ShiftID, ShiftName from Shift where Deleted='false'");
            return GetQuery(sql);
        }

        public static int insert_shift(Shift s)
        {
            string sql = string.Format(@"insert into shift(shiftid,shiftname,shiftbegin,shiftend,note,deleted)values(N'{0}',N'{1}',N'{2}',N'{3}',N'{4}',N'{5}')"
                , s.ShiftID, s.ShiftName , s.ShiftBegin, s.ShiftEnd, s.Note, s.Deleted);
            return Proccess(sql);
        }

        public static int update_shift(Shift s)
        {
            string sql = string.Format(@"update shift set shiftname=N'{0}',shiftbegin=N'{1}',shiftend=N'{2}',note=N'{3}' where shiftid='{4}'"
                , s.ShiftName, s.ShiftBegin, s.ShiftEnd, s.Note, s.ShiftID);
            return Proccess(sql);
        }

        public static int delete_shift(string shiftID)
        {
            string sql = string.Format(@"update shift set deleted='true' where shiftid='{0}'", shiftID);
            return Proccess(sql);
        }
        #endregion

        #region EMPLOYEE
        public static int getEmpIDMax()
        {
            int kq = 1;
            string sql = "select isnull(max(employeeid),0) from employee";
            DataTable dt = GetQuery(sql);
            kq = Convert.ToInt32(dt.Rows[0][0]) + 1;
            return kq;
        }

        public static string getEmpFullName(string EmpCode)
        {
            string EmpName = "";
            string sql = string.Format(@"select employeefirstname + employeelastname as 'EmpFullName'
                                        from employee where employeecode='{0}'", EmpCode);
            DataTable table = GetQuery(sql);
            if (table != null && table.Rows.Count > 0)
            {
                EmpName = table.Rows[0]["EmpFullName"].ToString();
            }
            return EmpName;
        }

        public static DataTable getAllEmpInfo()
        {
            string sql = @"select e.*,d.departmentname,p.positionname,r.rolename from employee e 
                left join department d on e.departmentid=d.departmentid 
                left join position p on e.positionid=p.positionid                 
                left join role r on e.roleid=r.roleid
                where e.deleted='false'
                order by e.employeelastname asc";
            return GetQuery(sql);
        }

        public static DataTable getEmpInfo(string EmpCode)
        {
            string sql = "";
            if (EmpCode != "0")
            {
                sql = string.Format(@"select e.EmployeeCode, e.EmployeeFirstName + e.EmployeeLastName as 'FullName'
                                    , d.DepartmentName, p.PositionName
                                    from Employee e 
                                    left join Department d On e.DepartmentID = d.DepartmentID 
                                    left join Position p On e.PositionID = p.PositionID  
                                    where EmployeeCode='{0}'", EmpCode);
            }
            else
            {
                sql = string.Format(@"select e.EmployeeCode, e.EmployeeFirstName + e.EmployeeLastName as 'FullName'
                                    , d.DepartmentName, p.PositionName
                                    from Employee e 
                                    left join Department d On e.DepartmentID = d.DepartmentID 
                                    left join Position p On e.PositionID = p.PositionID  
                                    where EmployeeCode != 0");
            }
            return GetQuery(sql);
        }

        public static DataTable getEmpInfo(string EmpCode, string DeptID)
        {
            string sql = "";
            if (DeptID == "0")
            {
                if (EmpCode != "0")
                {
                    sql = string.Format(@"select e.EmployeeCode, e.EmployeeFirstName + e.EmployeeLastName as 'FullName'
                                    , d.DepartmentName, p.PositionName
                                    from Employee e 
                                    left join Department d On e.DepartmentID = d.DepartmentID 
                                    left join Position p On e.PositionID = p.PositionID  
                                    where EmployeeCode='{0}'", EmpCode);
                }
                else
                {
                    sql = string.Format(@"select e.EmployeeCode, e.EmployeeFirstName + e.EmployeeLastName as 'FullName'
                                    , d.DepartmentName, p.PositionName
                                    from Employee e 
                                    left join Department d On e.DepartmentID = d.DepartmentID 
                                    left join Position p On e.PositionID = p.PositionID  
                                    where EmployeeCode != 0");
                }                
            }
            else
            {
                if (EmpCode != "0")
                {
                    sql = string.Format(@"select e.EmployeeCode, e.EmployeeFirstName + e.EmployeeLastName as 'FullName'
                                    , d.DepartmentName, p.PositionName
                                    from Employee e 
                                    left join Department d On e.DepartmentID = d.DepartmentID 
                                    left join Position p On e.PositionID = p.PositionID  
                                    where EmployeeCode='{0}'", EmpCode);
                }
                else
                {
                    sql = string.Format(@"select e.EmployeeCode, e.EmployeeFirstName + e.EmployeeLastName as 'FullName'
                                    , d.DepartmentName, p.PositionName
                                    from Employee e 
                                    left join Department d On e.DepartmentID = d.DepartmentID 
                                    left join Position p On e.PositionID = p.PositionID  
                                    where e.DepartmentID='{0}'", DeptID);
                }
            }
            return GetQuery(sql);
        }


        public static int insert_employee(Employee e)
        {
            string sql = string.Format(@"INSERT INTO [Restaurant].[dbo].[Employee]
                                       ([EmployeeID]
                                       ,[EmployeeCode]
                                       ,[EmployeeFirstName]
                                       ,[EmployeeLastName]
                                       ,[EmployeeUserName]
                                       ,[EmployeePassWord]
                                       ,[EmployeeAddress]
                                       ,[EmployeeSex]
                                       ,[EmployeeBirthday]
                                       ,[EmployeePhone]
                                       ,[Deleted]
                                       ,[Note]
                                       ,[DepartmentID]
                                       ,[PositionID]
                                       ,[RoleID]
                                       ,[ShiftID_1]
                                       ,[ShiftID_2])
                                 VALUES (N'{0}',N'{1}',N'{2}',N'{3}',N'{4}',N'{5}',N'{6}',N'{7}',N'{8}'
                                       ,N'{9}',N'{10}',N'{11}',N'{12}',N'{13}',N'{14}',N'{15}',N'{16}')"
                                ,e.EmpID,e.EmpCode,e.EmpFirstName,e.EmpLastName,e.EmpUser,e.EmpPass
                                ,e.EmpAddress,e.EmpSex,e.EmpBirthday,e.EmpPhone,e.Deleted,e.Note
                                ,e.DepartmentID,e.PositionID,e.RoleID,e.ShiftID1,e.ShiftID2);
            return Proccess(sql);
        }

        public static int update_employee(Employee e)
        {
            string sql = string.Format(@"UPDATE [Restaurant].[dbo].[Employee]
                               SET 
                                   [EmployeeCode] = N'{0}'
                                  ,[EmployeeFirstName] = N'{1}'
                                  ,[EmployeeLastName] = N'{2}'
                                  ,[EmployeeUserName] = N'{3}'
                                  ,[EmployeePassWord] = N'{4}'
                                  ,[EmployeeAddress] = N'{5}'
                                  ,[EmployeeSex] = N'{6}'
                                  ,[EmployeeBirthday] = N'{7}'
                                  ,[EmployeePhone] = N'{8}'
                                  ,[Deleted] = N'{9}'
                                  ,[Note] = N'{10}'
                                  ,[DepartmentID] = N'{11}'
                                  ,[PositionID] = N'{12}'
                                  ,[RoleID] = N'{13}'
                                  ,[ShiftID_1] = N'{14}'
                                  ,[ShiftID_2] = N'{15}'
                             WHERE [EmployeeID] = N'{16}'",e.EmpCode,e.EmpFirstName,e.EmpLastName,e.EmpUser,e.EmpPass
                                                          ,e.EmpAddress,e.EmpSex,e.EmpBirthday,e.EmpPhone,e.Deleted,e.Note
                                                          ,e.DepartmentID,e.PositionID,e.RoleID,e.ShiftID1,e.ShiftID2,e.EmpID);
            return Proccess(sql);
        }

        public static int delete_employee(int empCode)
        {
            string sql = string.Format(@"update employee set deleted='true' where employeecode=N'{0}'", empCode);
            return Proccess(sql);
        }

        public static string[] SplitEmpName(string EmpName)
        {
            int index = EmpName.LastIndexOf(" ");            
            string[] a = new string[2];
            a[0] = EmpName.Substring(0, index+1);
            a[1] = EmpName.Substring(index+1, EmpName.Length-index-1);
            return a;
        }

        public static DataTable getEmpShiftInfo(string EmpCode)
        {
            string sql = string.Format(@"select s.shiftbegin, s.shiftend from employee e left join shift s
                         on e.shiftid_1 = s.shiftid or e.shiftid_2 = s.shiftid where e.employeecode=N'{0}' ", EmpCode);
            return GetQuery(sql);
        }

        public static DataTable GetEmployeeShift(string EmpCode, DateTime DateBegin)
        {
            DataTable EmpShiftList = getEmpShiftInfo(EmpCode);
            DataTable table = EmpShiftList.Clone();
            if (EmpShiftList.Rows.Count > 1)
            {
                foreach (DataRow row in EmpShiftList.Rows)
                {
                    if (DateBegin.Hour >= DateTime.Parse(row["ShiftBegin"].ToString()).Hour &&
                        DateBegin.Hour <= DateTime.Parse(row["ShiftEnd"].ToString()).Hour)
                    {
                        DataRow datarow = table.NewRow();
                        datarow[0] = string.Format("HH:mm:ss", row["ShiftBegin"]);
                        datarow[1] = string.Format("HH:mm:ss", row["ShiftEnd"]);
                        table.Rows.Add(datarow);
                    }
                }
                return table;
            }
            return EmpShiftList;
        }

        public static int getTotalShiftMinutes(DataTable dt_EmpShift)
        {
            try
            {
                int total = 0;
                for (int i = 0; i < dt_EmpShift.Rows.Count; i++)
                {
                    total += (int)(Convert.ToDateTime(dt_EmpShift.Rows[i]["shiftend"]) - Convert.ToDateTime(dt_EmpShift.Rows[i]["shiftbegin"])).TotalMinutes;
                }
                return total;
            }
            catch (Exception)
            {
                return 480;
            }
        }

        public static DateTime convertToDateTime(DateTime time)
        {
            DateTime newTime = Convert.ToDateTime(string.Format("{0:HH:mm:ss}", time));
            return newTime;
        }
        #endregion

        #region ROLE

        public static DataTable getRoleList()
        {
            string sql = string.Format(@"select RoleID, RoleName from Role");
            return GetQuery(sql);
        }

        #endregion

        #region SET
        public static int GetSetIDMax()
        {
            int kq = 1;
            string sql = "select isnull(max(ID),0) from [set]";
            DataTable dt = GetQuery(sql);
            kq = Convert.ToInt32(dt.Rows[0][0]) + 1;
            return kq;
        }

        public static DataTable GetSetList()
        {
            string sql = "select * from [Set]";
            return GetQuery(sql);
        }

        public static int update_Set(Set s)
        {
            string sql = string.Format(@"update [Set] set spacetime={0}, latetime={1}, 
            earlytime={2}, maxtime={3}, monthbegin={4}, logtime='{5}' where id={6}"
                , s.SpaceTime, s.LateTime, s.EarlyTime, s.MaxTime, s.MonthBegin, s.LogTime, s.SetID);
            return Proccess(sql);
        }

        public static int Get_set_spacetime()
        {
            int kq = 120;
            try
            {
                DataTable dt = GetQuery("select spacetime from [set]");
                kq = Convert.ToInt32(dt.Rows[0][0]);
            }
            catch { }
            return kq;
        }

        public static int Get_set_latetime()
        {
            int kq = 120;
            try
            {
                DataTable dt = GetQuery("select latetime from [set]");
                kq = Convert.ToInt32(dt.Rows[0][0]);
            }
            catch { }
            return kq;
        }

        public static int Get_set_earlytime()
        {
            int kq = 120;
            try
            {
                DataTable dt = GetQuery("select earlytime from [set]");
                kq = Convert.ToInt32(dt.Rows[0][0]);
            }
            catch { }
            return kq;
        }

        public static int Get_set_maxtime()
        {
            int kq = 120;
            try
            {
                DataTable dt = GetQuery("select maxtime from [set]");
                kq = Convert.ToInt32(dt.Rows[0][0]);
            }
            catch { }
            return kq;
        }

        public static int Get_set_monthbegin()
        {
            int kq = 120;
            try
            {
                DataTable dt = GetQuery("select monthbegin from [set]");
                kq = Convert.ToInt32(dt.Rows[0][0]);
            }
            catch { }
            return kq;
        }

        public static bool Get_set_logtime()
        {
            bool kq = false;
            try
            {
                DataTable dt = GetQuery("select logtime from [set]");
                kq = Convert.ToBoolean(dt.Rows[0][0]);
            }
            catch { }
            return kq;
        }

        #endregion

        #region EMP RICE

        public static DataTable GetEmpRiceList(DateTime fromDate, DateTime toDate)
        {
            string sql = string.Format(@"select r.*,e.EmployeeFirstName + e.EmployeeLastName as 'FullName',e.DepartmentID
                                        from EmpRice r left join Employee e
                                        on r.EmployeeID=e.EmployeeID
                                        where r.EmpRiceDate between '{0}' and '{1}'
                                        order by EmpRiceDate, EmployeeLastName", fromDate, toDate);
            return GetQuery(sql);
        }

        public static DataTable GetAllEmpRiceList()
        {
            string sql = string.Format(@"select r.*,e.EmployeeFirstName + e.EmployeeLastName as 'FullName',e.DepartmentID
                                        from EmpRice r left join Employee e
                                        on r.EmployeeID=e.EmployeeID");
            return GetQuery(sql);
        }

        public static DataTable GetEmpListByDeptID(string DepartmentID)
        {
            string sql = string.Format(@"select EmployeeID, EmployeeFirstname + EmployeeLastName as 'FullName'
                                        from Employee 
                                        where DepartmentID='{0}'", DepartmentID);
            return GetQuery(sql);
        }

        public static int insert_EmpRice(List<EmpRice> list)
        {
            string sql = string.Empty;
            foreach (var item in list)
            {
                sql += string.Format(@"insert into EmpRice values ('{0}','{1}','{2}')", item.EmpRiceDate
                    , item.EmpID, item.Note);
            }
            return Proccess(sql);
        }

        public static int delete_EmpRice(List<EmpRice> list)
        {
            string sql = string.Empty;
            foreach (var item in list)
            {
                sql += string.Format(@"delete from EmpRice where EmpRiceDate='{0}' and EmployeeID='{1}'"
                                        , item.EmpRiceDate
                                        , item.EmpID);
            }
            return Proccess(sql);
        }
        
        public static DataTable GetEmployeeList()
        {
            string sql = @"select EmployeeID,EmployeeFirstName+EmployeeLastName as 'FullName'
                from Employee";
            return GetQuery(sql);
        }

        public static DataTable Rice_getDepartmentList()
        {
            string sql = string.Format(@"select DepartmentID, DepartmentName from Department where Deleted='false'");
            return GetQuery(sql);
        }

        #endregion

        #region EVENT LOG

        public static DataTable getEventLog(DateTime fromDate, DateTime toDate)
        {
            string sql = string.Format(@"select l.*, e.EmployeeFirstName + e.EmployeeLastname as 'FullName'
                                        from LogEvent l left join Employee e 
                                        on l.EmployeeID=e.EmployeeID
                                        where EventDate between '{0}' and '{1}' order by EventDate", fromDate, toDate);
            return GetQuery(sql);
        }

        public static DataTable getEventType()
        {
            string sql = "select * from EventType";
            return GetQuery(sql);
        }

        public static DataTable getAllEventLog()
        {
            string sql = string.Format(@"select l.*, e.EmployeeFirstName + e.EmployeeLastname as 'FullName'
                                        from LogEvent l left join Employee e 
                                        on l.EmployeeID=e.EmployeeID");
            return GetQuery(sql);
        }

        public static int insert_EventLog(List<LogEvent> list)
        {
            string sql = string.Empty;
            foreach (var item in list)
            {
                sql += string.Format(@"insert into LogEvent values ('{0}','{1}','{2}','{3}') "
                    , item.EventDate, item.EventID, item.EmployeeID, item.Note);
            }
            return Proccess(sql);
        }

        public static int delete_EventLog(LogEvent log)
        {
            string sql = string.Format(@"delete from LogEvent where EmployeeID='{0}'
                                        and EventDate='{1}' and EventID='{2}'", log.EmployeeID, log.EventDate, log.EventID);
            return Proccess(sql);
        }

        #endregion
    }
}
